"""add_soft_delete_to_lead_property

Revision ID: 20251011_soft_delete
Revises: 20251011_ai_error_tracking
Create Date: 2025-10-11 15:00:00.000000

Add soft delete mechanism to Lead and Property models:
- is_deleted: boolean flag to mark deleted records
- deleted_at: timestamp when record was soft-deleted
- deleted_by: user who performed the deletion

This resolves PRD issue: "无软删除机制 - Lead、Property删除直接物理删除"
Benefits:
- Data can be recovered
- Maintains audit trail
- Supports compliance requirements
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import sqlite


# revision identifiers, used by Alembic.
revision = '20251011_soft_delete'
down_revision = '20251011_ai_error_tracking'
branch_labels = None
depends_on = None


def upgrade() -> None:
    """Add soft delete columns to leads and properties tables."""

    # Add soft delete columns to leads table
    op.add_column(
        'leads',
        sa.Column(
            'is_deleted',
            sa.Boolean(),
            nullable=False,
            server_default='0',
            comment='Soft delete flag'
        )
    )
    op.add_column(
        'leads',
        sa.Column(
            'deleted_at',
            sa.DateTime(),
            nullable=True,
            comment='Timestamp when record was soft-deleted'
        )
    )
    op.add_column(
        'leads',
        sa.Column(
            'deleted_by',
            sa.Integer(),
            nullable=True,
            comment='User ID who performed the deletion'
        )
    )

    # Create index on is_deleted for performance
    op.create_index(
        'ix_leads_is_deleted',
        'leads',
        ['is_deleted']
    )

    # Add soft delete columns to properties table
    op.add_column(
        'properties',
        sa.Column(
            'is_deleted',
            sa.Boolean(),
            nullable=False,
            server_default='0',
            comment='Soft delete flag'
        )
    )
    op.add_column(
        'properties',
        sa.Column(
            'deleted_at',
            sa.DateTime(),
            nullable=True,
            comment='Timestamp when record was soft-deleted'
        )
    )
    op.add_column(
        'properties',
        sa.Column(
            'deleted_by',
            sa.Integer(),
            nullable=True,
            comment='User ID who performed the deletion'
        )
    )

    # Create index on is_deleted for performance
    op.create_index(
        'ix_properties_is_deleted',
        'properties',
        ['is_deleted']
    )


def downgrade() -> None:
    """Remove soft delete columns from leads and properties tables."""

    # Drop indexes
    op.drop_index('ix_properties_is_deleted', table_name='properties')
    op.drop_index('ix_leads_is_deleted', table_name='leads')

    # Drop columns from properties
    op.drop_column('properties', 'deleted_by')
    op.drop_column('properties', 'deleted_at')
    op.drop_column('properties', 'is_deleted')

    # Drop columns from leads
    op.drop_column('leads', 'deleted_by')
    op.drop_column('leads', 'deleted_at')
    op.drop_column('leads', 'is_deleted')
